package me.chyxion.jdbc;

import lombok.val;
import java.util.*;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.lang.reflect.Array;
import java.util.regex.Pattern;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import java.sql.PreparedStatement;
import me.chyxion.jdbc.util.StringUtils;
import org.slf4j.helpers.MessageFormatter;
import me.chyxion.jdbc.pagination.PaginationProcessor;

/**
 * @version 0.0.1
 * @since 0.0.1
 * @author Shaun Chyxion <br>
 * chyxion@163.com <br>
 * Dec 20, 2015 5:28:00 PM
 */
@Slf4j
@NoArgsConstructor
@AllArgsConstructor
class BasicJdbcImpl implements BasicJdbc {
	private final Pattern PATTERN_PARAM_HOLDER = Pattern.compile(":\\w+");

	protected Connection conn;
	protected CustomResolver customResolver;

	/**
	 * {@inheritDoc}
	 */
	@Override
	public <T> List<T> listValue(final String sql, final Object... values) {
		return list(rs -> {
			if (rs.getMetaData().getColumnCount() > 1) {
				throw new IllegalStateException(fmt(
					"List Values By SQL [{}] Expected One Column To Be Returned, " +
					"But Found More Than One", sql));
			}
			return (T) customResolver.readValue(rs, 1);
		}, sql, values);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public <T> T findValue(final String sql, final Object... values) {
		return findOne(rs -> {
			if (rs.getMetaData().getColumnCount() > 1) {
				throw new IllegalStateException(fmt(
					"Find value by sql [{}] expected one column to be returned, " +
					"but found more than one", sql));
			}
			return (T) customResolver.readValue(rs, 1);
		}, sql, values);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public <T> T query(final Ro<T> rso,
					   final String sql,
					   final Object ... args) {
		return exec(new So<T>() {
			/**
			 * {@inheritDoc}
			 */
			@Override
			public Statement build() throws SQLException {
				return prepareStatement(conn, sql, Arrays.asList(args));
			}

			/**
			 * {@inheritDoc}
			 */
			@Override
			public T exec(Statement statement) throws SQLException {
				try (val rs = ps(statement).executeQuery()) {
					return rso.exec(rs);
				}
			}
		});
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public boolean execute(final String sql, final Object... args) {
		return exec(new So<Boolean>() {
			/**
			 * {@inheritDoc}
			 */
			@Override
			public Statement build() throws SQLException {
				return prepareStatement(conn, sql, Arrays.asList(args));
			}

			/**
			 * {@inheritDoc}
			 */
			@Override
			public Boolean exec(Statement statement) throws SQLException {
				return ps(statement).execute();
			}
		});
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public int executeBatch(final String sql,
							final int batchSize,
							final Collection<?>... args) {
		log.info("execute batch[{}]", sql);
		return exec(new So<Integer>() {

			/**
			 * {@inheritDoc}
			 */
			@Override
			public Statement build() throws SQLException {
				return conn.prepareStatement(sql);
			}

			/**
			 * {@inheritDoc}
			 */
			@Override
			public Integer exec(final Statement statement) throws SQLException {
				int result = 0;
				int i = 0;
				int bs = batchSize > 0 ? batchSize : 16;
				for (Collection<?> list : args) {
					setValues(ps(statement), list);
					ps(statement).addBatch();
					if (++i % bs == 0 && i != 0) {
						result += sum(statement.executeBatch());
					}
				}
				result += sum(statement.executeBatch());
				return result;
			}
		});
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public int executeBatch(final String sql,
							final int batchSize,
							final Collection<Collection<?>> args) {
		return executeBatch(sql, batchSize, args.toArray(new Collection<?>[0]));
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public int insert(final String table,
					  final Collection<String> cols,
					  final Collection<Collection<?>> values,
					  final int batchSize) {
		return executeBatch(genInsertSQL(table, cols),
				batchSize, values.toArray(new Collection<?>[0]));
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public int insert(final String table, final Map<String, ?> data) {
		val cols = data.keySet();
		val values = new ArrayList<>(cols.size());
		for (val col : cols) {
			values.add(data.get(col));
		}
		return update(genInsertSQL(table, cols), values);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public int update(final String sql, final Object... values) {
		return exec(new So<Integer>() {
			@Override
			public Statement build() throws SQLException {
				return prepareStatement(conn, sql, Arrays.asList(values));
			}

			@Override
			public Integer exec(final Statement statement) throws SQLException {
				return ps(statement).executeUpdate();
			}
		});
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Map<String, Object>> listMap(final String sql, final Object... values) {
		return list(rs -> readMap(rs), sql, values);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Map<String, Object>> listMapPage(final String sql,
												 final Collection<Order> orders,
												 final int start,
												 final int limit,
												 final Object... args) {

		val sa = customResolver.getPaginationProcessor(conn)
					.process(orders, start, limit, sql, new ArrayList<>(Arrays.asList(args)));
		return query(rs -> readMapList(rs), sa.getSql(), sa.getArgs());
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public Map<String, Object> findMap(final String sql, final Object... values) {
		return findOne(rs -> readMap(rs), sql, values);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public <T> T findOne(final Ro<T> ro,
						 final String sql,
						 final Object... values) {
		return query(rs -> {
			T result = null;
			if (rs.next()) {
				result = ro.exec(rs);
			}
			if (rs.next()) {
				throw new IllegalStateException(fmt(
					"Find one by sQL [{}] expected one result (Or NULL) to be returned, " +
					"but found more than one", sql));
			}
			return result;
		}, sql, values);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public <T> List<T> list(
		final Ro<T> ro, final String sql, final Object... args) {
		return query(rs -> {
			val result = new LinkedList<T>();
			while (rs.next()) {
				result.add(ro.exec(rs));
			}
			return result;
		}, sql, args);
	}

	// --
	// private methods

	private <T> T exec(So<T> so) {
		try (val statement = so.build()) {
			return so.exec(statement);
		}
		catch (SQLException e) {
			log.error("Execute statement error caused.", e);
			throw new IllegalStateException(e);
		}
	}

	private PreparedStatement ps(final Statement statement) {
		return (PreparedStatement) statement;
	}

	private Map<String, Object> readMap(final ResultSet rs) throws SQLException {
		val metaData = rs.getMetaData();
		val numColumn = metaData.getColumnCount();
		val mapRtn = new HashMap<String, Object>(numColumn * 2);

		for (int i = 1; i <= numColumn; ++i) {
			String colName = metaData.getColumnLabel(i);
			// ignore row number
			if (!PaginationProcessor.COLUMN_ROW_NUMBER
					.equalsIgnoreCase(colName)) {
				mapRtn.put(colName, customResolver.readValue(rs, i));
			}
		}
		return mapRtn;
	}

	private List<Map<String, Object>> readMapList(ResultSet rs) {
		val mapList = new ArrayList<Map<String, Object>>(64);
		try {
			while (rs.next()) {
				mapList.add(readMap(rs));
			}
		}
		catch (SQLException e) {
			log.error("Read map list error caused.", e);
			throw new IllegalStateException(e);
		}
		return mapList;
	}

	private PreparedStatement setValues(final PreparedStatement ps,
										final Collection<?> values) throws SQLException {
		if (values != null && !values.isEmpty()) {
			int i = 0;
			for (val value : values) {
				customResolver.setParam(ps, ++i, value);
			}
		}
		return ps;
	}

	/**
	 * @param sql delete from users where id = :id
	 * @param mapArgs {id: 2008110101}
	 * @param outArgs [] -> [2008110101]
	 * @return delete from users where id = ?
	 */
    private String buildSql(final String sql, final Map<String, ?> mapArgs, final List<Object> outArgs) {
        if (!mapArgs.isEmpty()) {
            val newSql = new StringBuffer();
            // matches such as :user_id, :id
            val matcher = PATTERN_PARAM_HOLDER.matcher(sql);
            while (matcher.find()) {
                val args = new LinkedList<>();
                // replace named arg ':user_id' to arg holder '?'
                matcher.appendReplacement(newSql,
                	genArgHolder(mapArgs.get(matcher.group().substring(1)), args));
                outArgs.addAll(args);
            }
            matcher.appendTail(newSql);
            return newSql.toString();
        }

		return sql;
    }

    /**
     * @param sql delete from users where id in (?)
     * @param args
     * @param outArgs
     * @return
     */
	private String buildSql(String sql, Collection<?> args, List<Object> outArgs) {
		val newSql = new StringBuilder();
		// avoid last ? could not be split
		val sqlFrags = (sql + " ").split("\\?");
		// no ? and values is empty
		if (sqlFrags.length == 1 && args.isEmpty()) {
			return newSql.append(sql).toString();
		}

		// expand n values for 1 '?'
		if (sqlFrags.length == 2 && !args.isEmpty()) {
			return newSql.append(sqlFrags[0])
				.append(genArgHolder(args, outArgs))
				.append(sqlFrags[1])
				.toString();
		}

		// size(?) == size(values)
		if (sqlFrags.length == args.size() + 1) {
			int i = 0;
			for (val arg : args) {
				val valuesExpanded = new LinkedList<Object>();
				newSql.append(sqlFrags[i++]);
				newSql.append(genArgHolder(arg, valuesExpanded));
				outArgs.addAll(valuesExpanded);
			}

			return newSql.append(sqlFrags[sqlFrags.length - 1])
					.toString();
		}

		// error
		throw new IllegalStateException(
			fmt("SQL [{}] does not match args [{}]", sql, args));
	}

	@SuppressWarnings("unchecked")
	private PreparedStatement prepareStatement(
			final Connection conn,
			final String sql,
			final Collection<?> args) throws SQLException {
		String newSql;
		val newArgs = new LinkedList<Object>();
		if (args.size() == 1) {
			val objArgs = args.iterator().next();
			if (objArgs != null && objArgs.getClass().isArray()) {
				val vh = new LinkedList<Object>();
				for (int i = 0; i < Array.getLength(objArgs); ++i) {
					vh.add(Array.get(objArgs, i));
				}
				newSql = buildSql(sql, vh, newArgs);
			}
			else if (objArgs instanceof Collection<?>) {
				newSql = buildSql(sql, (Collection<?>) objArgs, newArgs);
			}
			else if (objArgs instanceof Map<?, ?>) {
				newSql = buildSql(sql, (Map<String, ?>) objArgs, newArgs);
            }
            else {
				newSql = sql;
				newArgs.add(objArgs);
			}
		}
		else if (args.size() > 1) {
			newSql = buildSql(sql, args, newArgs);
		}
		else {
			newSql = sql;
		}

		val ps = conn.prepareStatement(newSql);
		// set new values
		if (!newArgs.isEmpty()) {
			setValues(ps, newArgs);
		}
		return ps;
	}

	/**
	 * generate arg holder
	 * @param arg if v is collection, generate '?, ?...' else ?
	 * @param argsOut args out
	 * @return sql ?, ?
	 */
	private String genArgHolder(Object arg, List<Object> argsOut) {
		if (arg != null && arg.getClass().isArray()) {
			val vh = new LinkedList<String>();
			for (int i = 0; i < Array.getLength(arg); ++i) {
		        argsOut.add(Array.get(arg, i));
		        vh.add("?");
		    }
			return StringUtils.join(vh, ", ");
		}

		if (arg instanceof Collection<?>) {
			val listValues = (Collection<?>) arg;
			argsOut.addAll(listValues);
			val vh = new String[listValues.size()];
			Arrays.fill(vh, "?");
			return StringUtils.join(Arrays.asList(vh), ", ");
		}

		argsOut.add(arg);
		return "?";
	}

	private String genInsertSQL(String table, Collection<String> cols)  {
		val vh = new String[cols.size()];
		Arrays.fill(vh, "?");
		return new StringBuffer("insert into ")
			.append(table)
			.append(" (")
			.append(StringUtils.join(cols, ", "))
			.append(") values (")
			.append(StringUtils.join(Arrays.asList(vh), ", "))
			.append(")")
			.toString();
	}

	private int sum(int[] v) {
		int result = 0;
		for (int i : v) {
			result += i;
		}
		return result;
	}

	private String fmt(String msg, Object... args) {
		return MessageFormatter.format(msg, args).getMessage();
	}
}
